package DB;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import Gui.Terminal;
import Model.Product;

/**
 * 
 * @author Allan Malka
 *
 */
public class DBProduct 
{
	//If anything changes in the database, then this should be easy to find and edit:
	private final String productID = "ProductID";
	private final String name = "name";
	private final String purchasePrice = "purchasePrice";
	private final String salesPrice = "salesPrice";
	private final String countryOfOrigin = "countryOfOrigin";
	private final String productType = "ProductType";
	private final String stock = "stock";
	private final String minStock = "minStock";
	
	//String builder
	private final String selectAll = String.format("SELECT %s, %s, %s, %s, %s, %s, %s, %s FROM Product", 
													productID, productType, name, purchasePrice, salesPrice, countryOfOrigin, stock, minStock);
	private final String createProduct = String.format("INSERT INTO Product %s, %s, %s, %s, %s, %s, %s VALUES ", 
														productID, name, purchasePrice, salesPrice, countryOfOrigin, stock, minStock);
	private final String deleteProduct = "DELETE FROM Product";
	/*private final String updateProduct = String.format("UPDATE Product SET %s, %s, %s, %s, %s, %s, %s", 
														productID, name, purchasePrice, salesPrice, countryOfOrigin, stock, minStock);*/
	
	//Connection
	private DBConnection conn;
	
	public DBProduct()
	{
		conn = DBConnection.getInstance();
	}
	

	public Product getProduct(String _prodID)
	{
		Product result = null;
		
		//Create statement
		Statement stmt = null;
		try {
			stmt = conn.getDBcon().createStatement();
		}
		catch(SQLException sE) {
			Terminal.say("An error occured during statement creation. \n Stack trace: ");
			Terminal.say(sE.getStackTrace().toString());
		}
		
		//Execute statement
		String condition = WhereAdder(selectAll, productID + " = " +_prodID);
		
		try {
			stmt.execute(condition);
		}
		catch(SQLException sE) {
			Terminal.say("An error occured executing the statement.");
			Terminal.say(condition);
		    Terminal.say("Stack trace: ");
			Terminal.say(sE.getStackTrace().toString());			
		}
		
		//Get result
		ResultSet resSet = null;
		try {
			resSet = stmt.getResultSet();
		}
		catch(SQLException sE) {
			Terminal.say("The resultSet could not be retrieved. \n Stack trace: ");
			Terminal.say(sE.getStackTrace().toString());
		}
		
		//Build the products
		if(resSet != null) {
			result = BuildProduct(resSet);
		}
		else {
			Terminal.say("No result was found");
		}
		
		//Close connection
		if(stmt != null) {
			try {
				stmt.close();
			}
			catch(SQLException sE) {
				Terminal.say("Stack trace: ");
				Terminal.say(sE.getStackTrace().toString());
			}
		}
		
		return result;
	}
	
	public ArrayList<Product> getAllProducts()
	{
		ArrayList<Product> allProducts = new ArrayList<Product>();
		
		//Create statement
		Statement stmt = null;
		try {
			stmt = conn.getDBcon().createStatement();
		}
		catch(SQLException sE) {
			Terminal.say("Stack Trace: ");
			Terminal.say(sE.getStackTrace().toString());
		}
		
		//Execute statement
		try {
			stmt.execute(selectAll);
		}
		catch(SQLException sE) {
			Terminal.say("Stack Trace: ");
			Terminal.say(sE.getStackTrace().toString());
		}
		
		//Retrieve results
		ResultSet resSet = null;
		try {
			resSet = stmt.getResultSet();
		}
		catch(SQLException sE) {
			Terminal.say("Stack Trace: ");
			Terminal.say(sE.getStackTrace().toString());
		}
		
		//Build an Product object for each result
		try {
			do {
				allProducts.add(BuildProduct(resSet));
			} while(resSet.next());
		}
		catch(SQLException sE) {
			Terminal.say("Stack Trace: ");
			Terminal.say(sE.getStackTrace().toString());
		}
		
		//Close connection
		if(stmt != null) {
			try {
				stmt.close();
			}
			catch(SQLException sE) {
				Terminal.say("Stack Trace: ");
				Terminal.say(sE.getStackTrace().toString());
			}
		}
		
		return allProducts;
	}
	
	private String WhereAdder(String queryString, String condition)
	{
		return queryString + " WHERE " + condition;
	}
	
	public String getProductName(String productID)
	{
		String productName = null;
		//Create statement
				Statement stmt = null;
				try {
					stmt = conn.getDBcon().createStatement();
				}
				catch(SQLException sE) {
					Terminal.say("Stack Trace: ");
					Terminal.say(sE.getStackTrace().toString());
				}
		
		//Execute statement
				try {
					stmt.execute("SELECT Product.name FROM Product WHERE " + this.productID + " = " + productID);
				}
				catch(SQLException sE) {
					Terminal.say("Stack Trace: ");
					Terminal.say(sE.getStackTrace().toString());
				}
				
		//Retrieve result
				ResultSet resSet = null;
				try {
					resSet = stmt.getResultSet();
					if(resSet.next())
					{
						productName = resSet.getString("name");
					}
					
				} catch (SQLException e) {
					Terminal.say("An error occured", e);
				}
				
		//Close
				try {
					stmt.close();
				} catch (SQLException e) {
					Terminal.say("An error occured", e);
				}
				
				return productName;
	}
	
	private Product BuildProduct(ResultSet resSet)
	{
		try {
			//ResultSet starts at -1, so a first next() call is needed to find the first item.
			
			if(resSet.next()) {
				String prodID = resSet.getString(productID);
				String prodType = resSet.getString(productType);
				String name = resSet.getString(this.name);
				double pPrice = resSet.getDouble(purchasePrice);
				double sPrice = resSet.getDouble(salesPrice);
				String cOO = resSet.getString(countryOfOrigin);
				int stock = resSet.getInt(this.stock);
				int minStock = resSet.getInt(this.minStock);
				
				return new Product(prodID, name, pPrice, sPrice, cOO, prodType, stock, minStock);
			}
			else {
				return null;
			}
		}
		catch(SQLException sE) {
			Terminal.say("Could not build Product object \n Stack trace: ");
			Terminal.say(sE.getStackTrace().toString());
			
			return null;
		}
	}
	
	public int InsertProduct(Product prod)
	{
		int rc = -1;
		String newID = String.format("SELECT MAX(CAST(value AS Int)) FROM %s WHERE VALUE NOT LIKE '%[a-z]%' AND ISNUMERIC(VALUE) = 1", "Product");
		newID += 10;
		String insertQuery = createProduct + newID + ", " + prod.getName() + ", " + prod.getPurchasePrice() + ", " 
							+ prod.getSalesPrice() + ", " + prod.getCountryOfOrigin() + ", " + prod.getStock() + ", " + prod.getMinStock();
		
		//Create statement
		Statement stmt = null;
		try {
			stmt = conn.getDBcon().createStatement();
		}
		catch(SQLException sE) {
			Terminal.say("An error occured during statement creation for insert. \n Stack trace: ");
			Terminal.say(sE.getStackTrace().toString());
		}
		
		
		//Execute statement
		try {
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(insertQuery);
		}
		catch(SQLException sE) {
			Terminal.say("An error occured executing the statement for insert.");
			Terminal.say(createProduct);
		    Terminal.say("Stack trace: ");
			Terminal.say(sE.getStackTrace().toString());
		}
		
		
		//Close connection
		if(stmt != null) {
			try {
				stmt.close();
			}
			catch(SQLException sE) {
				Terminal.say("Stack trace: ");
				Terminal.say(sE.getStackTrace().toString());
			}
		}
		return (rc);
	}
	
	public boolean DeleteProduct(String id)
	{
		boolean control = false;
		Statement stmt = null;
		
		try {
			stmt = conn.getDBcon().createStatement();
			stmt.setQueryTimeout(5);
			stmt.execute(WhereAdder(deleteProduct, id));
			stmt.close();
			control = true;
		}
		catch(SQLException sE) {
			Terminal.say("Stack trace: ");
			Terminal.say(sE.getStackTrace().toString());
		}
		return control;
	}
	
	public String getHighestNo()
	{
		Statement stmt = null;
		
		try {
			stmt = conn.getDBcon().createStatement();
		} catch (SQLException e) {
			Terminal.say("Could not create statement to delete invoice to DB", e);
		}
		
		try {
			stmt.execute(String.format("SELECT MAX(CAST(%s AS Int)) AS Res FROM Product", this.productID));
		} catch (SQLException e) {
			Terminal.say("Could not find highest productID no", e);
		}
		
		ResultSet resSet = null;
		
		try {
			resSet = stmt.getResultSet();
		} catch (SQLException e) {
			Terminal.say("Could not get ResultSet from getHighestNo", e);
		}
		
		String result = null;
		try {
			resSet.next();
			int hiNo = resSet.getInt("Res");
			result = hiNo + "";
		}
		catch(Exception e) {
			Terminal.say("En Error occured in getHighestNo()", e);
		}
		
		try
		{
			stmt.close();
		}
		catch(Exception exception)
		{
			Terminal.say("Could not close");
		}
		return result;
	}

	public void updateProduct(String _prodID, String _name, double _pPrice, double _sPrice, String _country, String _prodType, int _stock, int _minStock)
	{
		Statement stmt = null;
		try {
			stmt = conn.getDBcon().createStatement();
		}
		catch(SQLException sE) {
			Terminal.say("Could not create statement: \n" + sE.getStackTrace());
		}
		String updateQuery = String.format("UPdATE Product SET ProductID = '%s', ProductType = '%s', name = '%s', Stock = '%s', PurchasePrice = '%s', SalesPrice = '%s', CountryOfOrigin = '%s', MinStock = '%s' FROM Product", 
				_prodID, _prodType, _name, _stock, _pPrice, _sPrice, _country, _minStock);
		
		try {
			stmt.executeQuery(WhereAdder(updateQuery, "ProductID = " + _prodID));
		}
		catch(SQLException sE) {
			Terminal.say("Error in executing: \n" + updateQuery);
		}

		try {
			stmt.close();
		} catch (SQLException e) {
			Terminal.say("Close Statement error", e);
		}
	}

}
